﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.OleDb;

namespace MWTWorkUnitMgmtTool.DataAccess.TableGatewayRepository
{
    public class MappingRepository:IMappingRepository
    {
        string _connectionString;
        private ITableGatewayRepository _tableGatewayRepository; 
        public MappingRepository(ITableGatewayRepository tableGatewayRepository)
        {
            _connectionString = ConfigurationManager.AppSettings["DB_SAPB1Gateway"].ToString(); 
            _tableGatewayRepository = tableGatewayRepository; 
        }

        #region IMappingRepository Members

        public IEnumerable<System.Data.IDataRecord> GetMappingsByBOM(string itemCode)
        {
            string sql = string.Format("SELECT ID, WorkUnitCode, LocationCode, Mapping.ItemCode, OITM.ItemName FROM tblWorkunitItemCodeLocationMapping Mapping INNER JOIN [MWTEP].dbo.OITM OITM  ON Mapping.ItemCode = OITM.ItemCode COLLATE database_default WHERE OITM.ItemName LIKE '{0}'", itemCode + "%");
            return _tableGatewayRepository.Retrieve(sql, _connectionString, delegate(SqlParameterCollection p)
            {
               
            }); 
        }

        public int InsertMapping(string workUnitCode, string locationCode, string itemCode)
        {
            return _tableGatewayRepository.Commit("INSERT INTO tblWorkUnitItemCodeLocationMapping VALUES(@WorkUnitCode, @LocationCode, @ItemCode)", _connectionString, delegate(SqlParameterCollection p)
            {
                p.Add("@WorkUnitCode", System.Data.SqlDbType.VarChar).Value = workUnitCode;
                p.Add("@LocationCode", System.Data.SqlDbType.VarChar).Value = locationCode;
                p.Add("@ItemCode", System.Data.SqlDbType.VarChar).Value = itemCode;
            });
        }

        public int UpdateMapping(string workUnitCode, string locationCode, string itemCode)
        {
            return _tableGatewayRepository.Commit("UPDATE tblWorkUnitItemCodeLocationMapping SET LocationCode=@LocationCode, ItemCode=@ItemCode WHERE WorkUnitCode=@WorkUnitCode", _connectionString, delegate(SqlParameterCollection p)
            {
                p.Add("@WorkUnitCode", System.Data.SqlDbType.VarChar).Value = workUnitCode;
                p.Add("@LocationCode", System.Data.SqlDbType.VarChar).Value = locationCode;
                p.Add("@ItemCode", System.Data.SqlDbType.VarChar).Value = itemCode;
            });
        }

        public int DeleteMapping(string workUnitCode, string locationCode, string itemCode)
        {
            return _tableGatewayRepository.Commit("DELETE FROM tblWorkUnitItemCodeLocationMapping WHERE WorkUnitCode=@WorkUnitCode AND LocationCode=@LocationCode AND ItemCode=@ItemCode", _connectionString, delegate(SqlParameterCollection p)
            {
                p.Add("@WorkUnitCode", System.Data.SqlDbType.VarChar).Value = workUnitCode;
                p.Add("@LocationCode", System.Data.SqlDbType.VarChar).Value = locationCode;
                p.Add("@ItemCode", System.Data.SqlDbType.VarChar).Value = itemCode;
            });
        }

        public IEnumerable<System.Data.IDataRecord> GetMappingsByID(int id)
        {
            string sql = "SELECT ID, WorkUnitCode, LocationCode, Mapping.ItemCode, OITM.ItemName FROM tblWorkunitItemCodeLocationMapping Mapping INNER JOIN [MWTEP].dbo.OITM OITM  ON Mapping.ItemCode = OITM.ItemCode COLLATE database_default WHERE Mapping.ID = @ID";
            return _tableGatewayRepository.Retrieve(sql, _connectionString, delegate(SqlParameterCollection p)
            {
                p.Add("@ID", System.Data.SqlDbType.Int).Value = id;
            });
        }

        public void UploadMappingsToDB(string filePath)
        {
            System.Data.OleDb.OleDbConnection oconn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath.ToString() + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";");
            oconn.Open();

            try
            {
                if (oconn.State == System.Data.ConnectionState.Closed)
                    oconn.Open();
            }
            catch (Exception ex)
            {
                //MessageBox.Show(ex.Message);
            }

            dynamic myTableName = oconn.GetSchema("Tables").Rows[0][2];
            OleDbCommand ocmd = new OleDbCommand("select * from [" + myTableName + "]", oconn);
            using (OleDbDataReader dr = ocmd.ExecuteReader())
            {
                SqlConnection connection = new SqlConnection(_connectionString);
                connection.Open();
                while (dr.Read())
                {
                    int returnValue = InsertMapping(dr[0].ToString(), dr[1].ToString(), dr[2].ToString()); 
                }
                oconn.Close();
                connection.Close();
            }
        }

        public IEnumerable<System.Data.IDataRecord> GetAllMappings()
        {
            string sql = "SELECT ID, WorkUnitCode, LocationCode, Mapping.ItemCode, OITM.ItemName FROM tblWorkunitItemCodeLocationMapping Mapping INNER JOIN [MWTEP].dbo.OITM OITM  ON Mapping.ItemCode = OITM.ItemCode COLLATE database_default";
            return _tableGatewayRepository.Retrieve(sql, _connectionString, delegate(SqlParameterCollection p)
            {

            }); 
        }

        #endregion



       
    }
}
